In this tutorial I will show you how to save excel file data in to MySQL database using Python.
CODE:
import pymysql
import pandas as pd
con = pymysql.connect(host='localhost', user='root',
passwd='password', charset='utf8')
cur = con.cursor()
cur.execute('create database EmpyloyeeDB character set utf8')
cur.execute('use EmpyloyeeDB')# get documentdf = pd.read_excel ("data/file_emp1.xls")sqlSentence1 = 'create table Employee(Id int,First_Name VARCHAR(20), Last_Name VARCHAR(20), Gender VARCHAR(10), Country VARCHAR(50))'cur.execute(sqlSentence1)# Get the length of the documentlength = len(df)for i in range ( 0 , length ):# data conversion character typerecord = tuple(df.loc[i])# insert table datasqlSentence = "INSERT INTO Employee (Id,First_Name, Last_Name, Gender, Country) VALUES ( %s , %s , %s , %s , %s)"# Fill the empty value, or missingsqlSentence = sqlSentence.replace('nan', 'null').replace('None', 'null').replace('none', 'null')# Print sequentially according to the loopcur.executemany(sqlSentence, [record])# end, closecur.close()con.commit()con.close()
After pasting the code you will get this below error.
"import pymysql could not be resolved from source pylance" To resolve this error you need to install pymsql.
pip install pymsql
VIDEO GUIDE:
Post your comments / questions
Recent Article
- The request was aborted: Could not create SSL/TLS secure channel -Error in Asp.net
- FieldError: Cannot resolve keyword 'id' into field in Django project
- How to hide the ID field from the Django admin?
- It is impossible to add a non nullable field without specifying a default. Django error
- ImportError: cannot import name 'url' from 'django.conf.urls' - Django Error
- How to Enable Virtualization in BIOS Security Settings in Intel Processors For Android Studio?
- Dependency 'androidx.activity:activity:1.8.0' requires libraries and applications that depend on it.
- AttributeError: 'NoneType' object has no attribute 'get_text' - Python
Related Article